package huang.teamwork11;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentConcrol {
		//创建添加学生信息的方法：
		public boolean studentAdd(Student stu){
			BaseDao bd=new BaseDao();
			Connection con=bd.getCon();
			//将学生信息添加到第一个表中；
			String sql1="insert into student(name,sex,weight,age) values(?,?,?,?)";
			//将学生信息添加到第二个表中
			String sql2="insert into scores(name,score) values(?,?)";
			try {
				PreparedStatement ps1=con.prepareStatement(sql1);
				PreparedStatement ps2=con.prepareStatement(sql2);
				ps1.setString(1, stu.getName());
				ps1.setString(2, stu.getSex());
				ps1.setFloat(3, stu.getWeight());
				ps1.setInt(4, stu.getAge());
				ps2.setString(1, stu.getName());
				ps2.setFloat(2, stu.getScore());
				ps1.execute();
				ps2.execute();
				if(ps1!=null){
					ps1.close();
				}
				if(ps2!=null){
					ps2.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return true;
		}
		//创建查询最差3名学生信息的方法；
		public boolean studentSelect(){
			BaseDao bd=new BaseDao();
			Connection con=bd.getCon();
			String sql="select s.name,ss.score from test.student s left join test.scores ss on s.name=ss.name order by ss.score limit 3;";
			try {
				PreparedStatement ps=con.prepareStatement(sql);
				ResultSet rs=ps.executeQuery();
				System.out.println("成绩最差的3名同学为：");
				while(rs.next()){
					System.out.println("姓名："+rs.getString("name")+"成绩："+rs.getFloat("score"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return true;
		}
}
